﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text.RegularExpressions;


namespace CCPSIP
{
    public class SipKit
    {
        private const string kOracleServer = "ISENDER.CCP.COM.TW";
        private const string kOracleUserId = "ISENDER";
        private static byte[] kOracleKey = { 0x61, 0x4B, 0x68, 0x4A, 0x4A, 0x46, 0x41, 0x32, 0x73, 0x57, 0x66, 0x45, 0x73, 0x51, 0x7A, 0x30, 0x5A, 0x6D, 0x35, 0x69, 0x77, 0x51, 0x69, 0x39, 0x67, 0x6A, 0x69, 0x56, 0x4E, 0x44 };

        private const int kIdHeaderLength = 8;
        private const int kCheckcodeLength = 64;//SHA-256
        private const string kTimePasswordFormat = ",yyyyMMdd_HHmm";
        private const int kTimeDeviationRange = 10;//minutes
        private const string kAppName = "CCPSIP";

        private const string ForTestCheckCode = "kizuki33";

        /// <summary>
        /// 總公司行動分機前綴
        /// </summary>
        public const string kHQMobileExtPrefix = "88";
        /// <summary>
        /// 總公司行動分機RegEx Pattern
        /// </summary>
        private const string kHQMobileExtPattern = "^88[1-9][0-9]{2}[1-9]?$";
        /// <summary>
        /// 行動分機RegEx Pattern
        /// </summary>
        public const string kSIPNumPattern = "^(88|194)[0-9]{3,4}$";

        /// <summary>
        /// 取得 CCPSIP 的 Oracle 連線字串
        /// </summary>
        /// <returns>Oracle 連線字串</returns>
        private static string GetConnStrMIS()
        {
            return String.Format(
              "Data Source={0};Persist Security Info=True;User ID={1};Password={2}",
              kOracleServer, kOracleUserId, System.Text.Encoding.UTF8.GetString(kOracleKey));
        }

        /// <summary>
        /// 取得SIP User資訊
        /// </summary>
        /// <param name="idHeader">UUID 的開頭字串</param>
        /// <param name="checkcode">UUID 的 Time Based 驗證碼</param>
        /// <param name="req">HttpRequest物件</param>
        /// <returns>若通過驗證則回傳SipUserInfo物件，否則null</returns>
        public static SipUserInfo GetUserInfo(string idHeader, string checkcode, System.Web.HttpRequest req)
        {
            DateTime reqTime = DateTime.Now;
            string ipAddr = ChrisKit.getIPAddress(req);

            //【01】檢查參數
            //
            try
            {
                if (idHeader.Length < kIdHeaderLength || checkcode.Length < kCheckcodeLength)
                {
                    if (checkcode == ForTestCheckCode)
                    {
                        //do nothing
                    }
                    else
                    {
                        //AppleKit.writeLoginErrLog(idHeader, checkcode, kAppName, ipAddr);
                        return null;
                    }
                }
                checkcode = checkcode.ToLower();
            }
            catch
            {
                return null;
            }

            //【02】產生可容忍時間的字串List
            //
            string[] timeStrArray = new string[kTimeDeviationRange * 2 + 1];
            timeStrArray[0] = reqTime.ToString(kTimePasswordFormat);
            for (int i = 1; i <= kTimeDeviationRange; i++)
            {
                timeStrArray[i * 2 - 1] = reqTime.AddMinutes(i).ToString(kTimePasswordFormat);
                timeStrArray[i * 2] = reqTime.AddMinutes(-i).ToString(kTimePasswordFormat);
            }

            //【03-1】連結DB，篩選符合的User
            //
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();

                string selStr = "SELECT CountryCode, MobileNum, UUID, DeviceType,"
                  + "   SIP_Num, PPS_CODE, FullName, CustomName, UserType"
                  + " FROM reg_user"
                  + " WHERE Status='Y' AND UUID LIKE :UUID"
                  + " ORDER BY CreateTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("UUID", OracleType.VarChar).Value = idHeader + "%";
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                //【03-2】比對Hash字串
                //2014-10-03 加入測試用checkcode
                foreach (DataRow row in dataSet.Tables[0].Rows)
                {
                    string uuid = Convert.ToString(row["UUID"]);
                    foreach (string timeStr in timeStrArray)
                    {
                        if ((checkcode == HashKit.getHashStr(uuid + timeStr, HashType.SHA256)) ||
                            (checkcode == ForTestCheckCode))
                            return new SipUserInfo(row);
                    }
                }
            }
            catch (Exception ex)
            {//Skip all Exception
                ChrisKit.SkipVar(ex);
            }
            finally
            {
                connOracle.Close();
            }
            //AppleKit.writeLoginErrLog(idHeader, checkcode, kAppName, ipAddr);
            return null;
        }


        /// <summary>
        /// 取得SIP User資訊
        /// </summary>
        /// <param name="SIP_Num">User的SIP帳號</param>
        /// <param name="connOracle">現有的OracleConnection</param>
        /// <returns>若有此帳號則回傳SipUserInfo物件，否則null</returns>
        public static SipUserInfo GetUserInfo(string SIP_Num, OracleConnection connOracle)
        {
            string selStr1 = "SELECT CountryCode, MobileNum, UUID, DeviceType,"
              + "   SIP_Num, PPS_CODE, FullName, CustomName, UserType"
              + " FROM reg_user"
              + " WHERE Status='Y' AND SIP_Num=:SIP_Num";
            OracleCommand selCmd1 = new OracleCommand(selStr1, connOracle);
            selCmd1.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
            OracleDataAdapter adapter1 = new OracleDataAdapter(selCmd1);
            DataSet dataSet1 = new DataSet();
            adapter1.Fill(dataSet1);

            if (dataSet1.Tables[0].Rows.Count > 0)
            {
                DataRow row0 = dataSet1.Tables[0].Rows[0];
                return new SipUserInfo(row0);
            }
            return null;
        }


        /// <summary>
        /// 取得 User 登入密碼計算規則
        /// </summary>
        /// <param name="SIP_Num">SIP帳號</param>
        /// <returns>SIP登入密碼計算規則</returns>
        public static SipSecretRule getSipSecretRule(string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT UUID, HashType, HashStart, HashLen FROM reg_user"
                  + " WHERE SIP_Num=:SIP_Num AND Status='Y'"
                  + " ORDER BY CreateTime DESC, pid DESC";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                if (dataSet.Tables[0].Rows.Count >= 1)
                {
                    return new SipSecretRule(dataSet.Tables[0].Rows[0]);
                }
                return null;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 計算SIP登入密碼
        /// </summary>
        /// <param name="SIP_Num">SIP帳號</param>
        /// <returns>SIP登入密碼</returns>
        public static string calcSipSecret(string SIP_Num)
        {
            SipSecretRule ruleObj = getSipSecretRule(SIP_Num);
            if (ruleObj == null)
            {
                return string.Format("SECRET_FAIL@{0:yyyyMMdd}", DateTime.Now);
            }
            else
            {
                string seedStr = string.Format("{0}_CCPSIP", ruleObj.UUID);
                HashType hashType = HashKit.getHashType(ruleObj.HashType);
                string HashStr = HashKit.getHashStr(seedStr, hashType);
                return HashStr.Substring(ruleObj.HashStart, ruleObj.HashLength);
            }
        }


        ///// <summary>
        ///// 取得員工編號對應的長春總公司分機號碼
        ///// </summary>
        ///// <param name="EmpID">員工編號</param>
        ///// <returns>有長春總公司分機則回傳分機號碼，否則null</returns>
        //public static string getCCPExtHQ(string EmpID) {
        //  if (string.IsNullOrEmpty(EmpID))
        //    return null;
        //  OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
        //  try {
        //    connOracle.Open();
        //    string selStr = "SELECT LocalNumber, DataSource FROM ccpaddrbook.phone_numbers"
        //      + " WHERE AreaCode='62' AND PPS_CODE=?PPS_CODE"
        //      + " ORDER BY CREATE_TIME, pid";
        //    OracleCommand selCmd = new OracleCommand(selStr, connOracle);
        //    selCmd.Parameters.Add("?PPS_CODE", OracleType.VarChar).Value = EmpID;
        //    OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
        //    DataSet dataSet = new DataSet();
        //    adapter.Fill(dataSet);
        //    if (dataSet.Tables[0].Rows.Count == 1) {
        //      DataRow row = dataSet.Tables[0].Rows[0];
        //      return Convert.ToString(row["LocalNumber"]);
        //    }
        //    else {
        //      foreach (DataRow row in dataSet.Tables[0].Rows) {
        //        if (Convert.ToString(row["DataSource"]).StartsWith("62_"))//以總公司分機表為準
        //          return Convert.ToString(row["LocalNumber"]);
        //      }
        //    }
        //    return null;
        //  }
        //  finally {
        //    connOracle.Close();
        //  }
        //}


        /// <summary>
        /// 取得員工編號對應的長春總公司分機號碼(20150216, PHONE_HQ改成動態看PHONE_HQ_VIEW)
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>有長春總公司分機則回傳分機號碼，否則null</returns>
        public static string getCCPExtHQ(string EmpID)
        {
            if (string.IsNullOrEmpty(EmpID))
                return null;

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT PHONENUM FROM PHONE_HQ_VIEW"    //(20150216, PHONE_HQ改成動態看PHONE_HQ_VIEW)
                  + " WHERE PPS_CODE=:PPS_CODE AND MAINEXT='1'";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                return Convert.ToString(selCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得員工編號對應的內線號碼(區碼+分機)
        /// </summary>
        /// <param name="EmpID">員工編號</param>
        /// <returns>有內線號碼則回傳，否則null</returns>
        public static string getCCPExt(string EmpID)
        {
            if (string.IsNullOrEmpty(EmpID))
                return null;

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT AreaCode, LocalNumber FROM phone_numbers"
                  + " WHERE PPS_CODE=:PPS_CODE"
                  + " ORDER BY CREATE_TIME, pid";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("PPS_CODE", OracleType.VarChar).Value = EmpID;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count >= 1)
                {
                    DataRow row = dataSet.Tables[0].Rows[0];
                    return string.Format("{0}{1}", row["AreaCode"], row["LocalNumber"]);
                }
                return null;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 判斷是否為總公司行動分機
        /// </summary>
        /// <param name="SIP_Num">SIP分機號碼</param>
        /// <returns>總公司行動分機回傳true，否則false</returns>
        public static bool isHQMobileExt(string SIP_Num)
        {
            if (Regex.IsMatch(SIP_Num, kHQMobileExtPattern))
                return true;
            else
                return false;
        }


        /// <summary>
        /// 由總公司行動分機轉換桌機號碼
        /// </summary>
        /// <param name="mobileExt">總公司行動分機號碼</param>
        /// <returns>總公司桌機號碼或null</returns>
        public static string getHQDesktopExt(string mobileExt)
        {
            if (isHQMobileExt(mobileExt))
                return mobileExt.Substring(kHQMobileExtPrefix.Length, 3);
            else
                return null;
        }


        /// <summary>
        /// 取得 User 的 Custom Name
        /// </summary>
        /// <param name="SIP_Num">SIP分機號碼</param>
        /// <returns>有此 User 則回傳 Custom Name，否則 null</returns>
        public static string getCustomName(string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT CustomName FROM reg_user WHERE Status='Y' AND SIP_Num=:SIP_Num";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                return Convert.ToString(selCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得 User 的 Full Name
        /// </summary>
        /// <param name="SIP_Num">SIP分機號碼</param>
        /// <returns>有此 User 則回傳 Full Name，否則 null</returns>
        private static string getFullName(string SIP_Num)
        {
            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT FullName FROM reg_user WHERE Status='Y' AND SIP_Num=:SIP_Num";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                return Convert.ToString(selCmd.ExecuteScalar());
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得 CCPSIP User 的 Short Display Name
        /// </summary>
        /// <param name="SIP_Num">SIP分機號碼</param>
        /// <returns>有此 User 則回傳 Short Display Name，否則回傳 SIP_Num</returns>
        public static string getDispNameShort(string SIP_Num)
        {
            if (!isHQMobileExt(SIP_Num))
            {
                return getCustomName(SIP_Num);
            }

            OracleConnection connOracle = new OracleConnection(GetConnStrMIS());
            try
            {
                connOracle.Open();
                string selStr = "SELECT FullName, DeviceType FROM reg_user"
                  + " WHERE SIP_Num=:SIP_Num AND Status='Y'";
                OracleCommand selCmd = new OracleCommand(selStr, connOracle);
                selCmd.Parameters.Add("SIP_Num", OracleType.VarChar).Value = SIP_Num;
                OracleDataAdapter adapter = new OracleDataAdapter(selCmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);

                if (dataSet.Tables[0].Rows.Count >= 1)
                {
                    DataRow row = dataSet.Tables[0].Rows[0];
                    string DeviceType = Convert.ToString(row["DeviceType"]);
                    string iDeviceFamily = AppleKit.getIDeviceFamily(DeviceType);
                    return string.Format("{0} {1}", row["FullName"], iDeviceFamily);
                }
                return SIP_Num;
            }
            finally
            {
                connOracle.Close();
            }
        }


        /// <summary>
        /// 取得總公司桌機共振資訊Dictionary
        /// </summary>
        /// <param name="MobileExt">SIP行動分機號碼</param>
        /// <returns>桌機共振資訊Dictionary</returns>
        public static Dictionary<string, string> getForkInfo(string MobileExt)
        {
            string HQDesktopExt = SipKit.getHQDesktopExt(MobileExt);
            if (string.IsNullOrEmpty(HQDesktopExt))
            {
                return null;
            }

            //【01】取得IP PBX設定
            //
            Dictionary<string, string> UserDict = IPPbxKitV2.GetUserData(HQDesktopExt);
            string forwardNum = UserDict["cfphone"];//指定轉接
            List<string> forkList = new List<string>(3);
            forkList.Add(UserDict["forkphone1"]);//共振號碼1
            forkList.Add(UserDict["forkphone2"]);//共振號碼2
            forkList.Add(UserDict["forkphone3"]);//共振號碼3

            //【02】分析設定值
            //
            Dictionary<string, string> resultDict = new Dictionary<string, string>();
            resultDict["DesktopExt"] = HQDesktopExt;

            if (string.IsNullOrEmpty(forwardNum))
            {
                resultDict["Fork"] = forkList.Contains(MobileExt) ? "Y" : "N";
                resultDict["Forward"] = "N";
                resultDict["ForwardNumber"] = "";
            }
            else
            {
                if (forwardNum.StartsWith("0"))
                {
                    //去掉撥打外線的初始碼"0"
                    forwardNum = forwardNum.Remove(0, 1);
                }
                resultDict["Fork"] = "N";
                resultDict["Forward"] = "Y";
                resultDict["ForwardNumber"] = forwardNum;
            }

            return resultDict;
        }


        /// <summary>
        /// 由 ChatID 判斷是否為群組
        /// </summary>
        /// <param name="ChatID">聊天ID(對方帳號或ClientGID)</param>
        /// <returns>ChatID是群組回傳true，否則false</returns>
        public static bool isGroupID(string ChatID)
        {
            return (ChatID.StartsWith("G"));
        }


        /// <summary>
        /// 判斷 UserType 是否為 CCP 身分
        /// </summary>
        /// <param name="UserType">UserType代碼</param>
        /// <returns>是 CCP 身分回傳true，否則false</returns>
        public static bool isCCPUser(string UserType)
        {
            if (UserType.StartsWith("CCP"))
                return true;
            else
                return false;
        }

        public static bool VarifyPpsCode(string pps_code)
        {
            OracleConnection ConnOracle = new OracleConnection(GetConnStrMIS());
            string cmd_all = "SELECT PPS_CODE FROM PERSON_ALL_MVIEW"
                + " WHERE PPS_CODE = :PPS_CODE";

            OracleCommand Cmd = new OracleCommand(cmd_all, ConnOracle);
            Cmd.Parameters.Add(":PPS_CODE", OracleType.VarChar).Value = pps_code;

            ConnOracle.Open();
            bool ValidPpsCode = !string.IsNullOrEmpty(Convert.ToString(Cmd.ExecuteScalar()));
            ConnOracle.Dispose();

            return ValidPpsCode;
        }

    }


    /// <summary>
    /// SIP User資訊物件
    /// </summary>
    public class SipUserInfo
    {
        private string _CountryCode;//手機門號國碼
        private string _MobileNum;  //手機門號
        private string _UUID;      //Device UUID
        private string _DeviceType;//裝置類別
        private string _SIP_Num;   //SIP帳號
        private string _PPS_CODE;//長春員工編號
        private string _FullName;  //Full Name
        private string _CustomName;//Custom Name
        private string _UserType;  //User身分類別
        private string _DEPT_NO; //長春部門代碼

        public string CountryCode { get { return this._CountryCode; } }
        public string MobileNum { get { return this._MobileNum; } }
        public string UUID { get { return this._UUID; } }
        public string DeviceType { get { return this._DeviceType; } }
        public string SIP_Num { get { return this._SIP_Num; } }
        public string PPS_CODE { get { return this._PPS_CODE; } }
        public string FullName { get { return this._FullName; } }
        public string CustomName { get { return this._CustomName; } }
        public string UserType { get { return this._UserType; } }
        public string DEPT_NO { get { return this._DEPT_NO; } }


        public SipUserInfo(DataRow row)
        {
            _CountryCode = Convert.ToString(row["CountryCode"]);
            _MobileNum = Convert.ToString(row["MobileNum"]);
            _UUID = Convert.ToString(row["UUID"]);
            _DeviceType = Convert.ToString(row["DeviceType"]);
            _SIP_Num = Convert.ToString(row["SIP_Num"]);
            _PPS_CODE = Convert.ToString(row["PPS_CODE"]);
            _FullName = Convert.ToString(row["FullName"]);
            _CustomName = Convert.ToString(row["CustomName"]);
            _UserType = Convert.ToString(row["UserType"]);

            if (!string.IsNullOrEmpty(_PPS_CODE))
                _DEPT_NO = OracleKitUser.getDeptNo(_PPS_CODE);
            else
                _DEPT_NO = "";
        }
    }


    /// <summary>
    /// SIP登入密碼計算規則
    /// </summary>
    public class SipSecretRule
    {
        private string _UUID;    //Device UUID
        private string _HashType;//Hash類別
        private int _HashStart;  //Hash的起始Index
        private int _HashLength; //Hash的抓取長度

        public string UUID { get { return this._UUID; } }
        public string HashType { get { return this._HashType; } }
        public int HashStart { get { return this._HashStart; } }
        public int HashLength { get { return this._HashLength; } }

        public SipSecretRule(DataRow row)
        {
            _UUID = Convert.ToString(row["UUID"]);
            _HashType = Convert.ToString(row["HashType"]);
            _HashStart = Convert.ToInt32(row["HashStart"]);
            _HashLength = Convert.ToInt32(row["HashLen"]);
        }
    }


}